﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Linq;
using StudentApartment.DataAccessLayer;
using StudentApartment.DataAccessHelper;

namespace StudentApartment.BusinessLogicLayer
{
    public class Student
    {
        private string studentNumber;
        private string studentName;
        private string studentSex;
        private string studentState;
        private string collegeName;
        private string studentProfession;
        private DateTime studentBirthday;
        private string studentAddress;
        private string studentNotes;
        private string errMessage; 

        #region MyRegion
        public string StudentNumber
        {
            set
            {
                this.studentNumber = value;
            }
            get
            {
                return this.studentNumber;
            }
        }

        public string StudentName
        {
            set
            {
                this.studentName = value;
            }
            get
            {
                return this.studentName;
            }
        }

        public string StudentSex
        {
            set
            {
                this.studentSex = value;
            }
            get
            {
                return this.studentSex;
            }
        }

        public string StudentState
        {
            set
            {
                this.studentState = value;
            }
            get
            {
                return this.studentState;
            }
        }

        
        public string CollegeName
        {
            set
            {
                this.collegeName = value;
            }
            get
            {
                return this.collegeName;
            }
        }

        public string StudentProfession
        {
            set
            {
                this.studentProfession = value;
            }
            get
            {
                return this.studentProfession;
            }
        }

        public DateTime StudentBirthday
        {
            set
            {
                this.studentBirthday = value;
            }
            get
            {
                return this.studentBirthday;
            }
        }

        public string StudentAddress
        {
            set
            {
                this.studentAddress = value;
            }
            get
            {
                return this.studentAddress;
            }
        }

        public string StudentNotes
        {
            set
            {
                this.studentNotes = value;
            }
            get
            {
                return this.studentNotes;
            }
        }

        public string ErrMessage
        {
            set
            {
                this.errMessage = value;
            }
            get
            {
                return this.errMessage;
            }
        }

        #endregion

        public Student()
        {
        }

        public Student(string studentNumber,string studentName,string studentSex,string studentState,string collegeName,
            string studentProfession,DateTime studentBirthday,string studentAddress,string studentNotes)
        {
            this.studentNumber = studentNumber;
            this.studentName = studentName;
            this.studentSex = studentSex;
            this.studentState = studentState;
            this.collegeName = collegeName;
            this.studentProfession = studentProfession;
            this.studentBirthday = studentBirthday;
            this.studentAddress = studentAddress;
            this.studentNotes = studentNotes;
        }

        public bool InsertStudent()
        {
            if (IsExist())
                return false; 

            string insertString = "insert into studentInfo(studentNumber,studentName,studentSex,";
            insertString = insertString + "studentState,collegeName,studentProfession,studentBirthday,";
            insertString = insertString + "studentAddress,studentNotes) values (";
            insertString = insertString + SqlString.GetQuotedString(this.studentNumber) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentName) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentSex) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentState) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.collegeName) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentProfession) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentBirthday.ToString()) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentAddress) + ",";
            insertString = insertString + SqlString.GetQuotedString(this.studentNotes) + ")";

            DataBase db = new DataBase();
            if (db.InsertOrUpdate(insertString) > 0)
            {
                this.ErrMessage = "学生信息添加成功!";
                return true;
            }
            else
            {
                this.ErrMessage = "添加学生信息失败!";
                return false;
            }
        }

        public bool IsExist()
        {
            bool isExist = true;
            string queryString = "select * from studentInfo where studentNumber=" + SqlString.GetQuotedString(studentNumber);
            DataBase db = new DataBase();
            if (db.GetRecord(queryString)==false)
                isExist = false;
            else
                this.ErrMessage = "此学生已存在";
            return isExist;  
        }

        public bool DeleteStudents(string studentNumbers)
        {
            string queryString = "select count(*) from accommodation where studentNumber in (" + studentNumbers + ")";
            DataBase db = new DataBase();
            int Count = db.GetRecordCount(queryString);
            if (Count > 0) 
            {
                this.ErrMessage = "有住宿信息的学生不能删除!";
                return false;
            }

            string str = "select count(*) from paymentInfo where studentNumber in(" + studentNumbers + ")";
            int count = db.GetRecordCount(str);
            if(count > 0) 
            {
                this.ErrMessage = "有交费信息的学生，不能删除!";
                return false;
            }

            string deleteString = "delete from studentInfo where studentNumber in (" + studentNumbers + ")";
            int effectCount = db.InsertOrUpdate(deleteString); 
            if(effectCount<0)
            {
                this.errMessage = "删除学生信息失败!";
                return false;
            }
            
            this.errMessage = "删除学生信息成功!";
            return true;

        }

        public void QueryStudentInfo(string studentNumber)
        {
            string queryString = "select * from studentInfo where studentNumber = " + SqlString.GetQuotedString(studentNumber);
            DataBase db = new DataBase();
            DataSet ds = db.GetDataSet(queryString); 
            if (ds != null)
            {
                studentNumber = ds.Tables[0].Rows[0]["studentNumber"].ToString();
                studentName = ds.Tables[0].Rows[0]["studentName"].ToString();
                studentSex = ds.Tables[0].Rows[0]["studentSex"].ToString();
                studentState = ds.Tables[0].Rows[0]["studentState"].ToString();
                collegeName = ds.Tables[0].Rows[0]["collegeName"].ToString();
                studentProfession = ds.Tables[0].Rows[0]["studentProfession"].ToString();
                studentBirthday = Convert.ToDateTime(ds.Tables[0].Rows[0]["studentBirthday"].ToString());
                studentAddress = ds.Tables[0].Rows[0]["studentAddress"].ToString();
                studentNotes = ds.Tables[0].Rows[0]["studentNotes"].ToString();
            }
        }

        public bool UpdateStudetnInfo()
        {
            string updateString = "update studentInfo set studentName = " + SqlString.GetQuotedString(this.studentName);
            updateString = updateString + ",studentSex=" + SqlString.GetQuotedString(this.studentSex);
            updateString = updateString + ",studentState=" + SqlString.GetQuotedString(this.studentState);
            updateString = updateString + ",collegeName=" + SqlString.GetQuotedString(this.collegeName);
            updateString = updateString + ",studentProfession=" + SqlString.GetQuotedString(this.studentProfession);
            updateString = updateString + ",studentBirthday='" + this.StudentBirthday + "'";
            updateString = updateString + ",studentAddress=" + SqlString.GetQuotedString(this.studentAddress);
            updateString = updateString + ",studentNotes=" + SqlString.GetQuotedString(this.studentNotes);
            updateString = updateString + " where studentNumber=" + SqlString.GetQuotedString(this.studentNumber);


            DataBase db = new DataBase();
            if (db.InsertOrUpdate(updateString) > 0) return true;

            return false;
        }

        public DataSet QueryStudent(string studentNumber, string studentName, string studentProfession)
        {
            string queryString = "select * from studentInfo where studentNumber like '%" + studentNumber + "%'";
            queryString = queryString + " and studentName like '%" + studentName + "%'";
            queryString = queryString + " and studentProfession like '%" + studentProfession + "%'";

            DataSet ds = (new DataBase()).GetDataSet(queryString);
            return ds;
        }

        public DataSet QueryAllStudent()
        {
            string queryString = "select * from studentInfo";
            DataSet ds = (new DataBase()).GetDataSet(queryString);
            return ds;
        }

        public DataSet QueryProfessionInfo()
        {
            string queryString = "select distinct professionName from professionInfo";
            DataSet ds = (new DataBase()).GetDataSet(queryString);
            return ds;
        }

        
    }

}
